import sqlite3
import os

def import_sql_backup(db_path, sql_file_path):
    # 检查文件是否存在
    if not os.path.exists(sql_file_path):
        print(f"错误：找不到SQL备份文件 {sql_file_path}")
        return False
    
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print(f"开始导入SQL备份文件：{sql_file_path}")
        
        # 读取SQL文件内容
        with open(sql_file_path, 'r', encoding='utf-8') as f:
            sql_script = f.read()
        
        # 分割SQL语句
        sql_statements = sql_script.split(';')
        
        # 执行每条SQL语句
        for statement in sql_statements:
            statement = statement.strip()
            if statement:
                try:
                    # 对于CREATE TABLE语句，如果表已存在则跳过
                    if statement.upper().startswith('CREATE TABLE'):
                        # 尝试获取表名
                        table_name_start = statement.upper().find('CREATE TABLE') + len('CREATE TABLE')
                        # 处理可能的IF NOT EXISTS
                        if 'IF NOT EXISTS' in statement.upper()[table_name_start:table_name_start+50]:
                            table_name_start = statement.upper().find('IF NOT EXISTS') + len('IF NOT EXISTS')
                        
                        # 提取表名（去掉可能的引号和空格）
                        table_name_part = statement[table_name_start:].strip()
                        if table_name_part.startswith('"') or table_name_part.startswith('`'):
                            table_name = table_name_part.split(table_name_part[0])[1]
                        else:
                            table_name = table_name_part.split()[0]
                        
                        # 检查表是否已存在
                        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
                        if cursor.fetchone():
                            print(f"表 {table_name} 已存在，跳过创建")
                            continue
                    
                    # 执行SQL语句
                    cursor.execute(statement)
                    print(f"执行成功: {statement[:50]}..." if len(statement) > 50 else f"执行成功: {statement}")
                except sqlite3.Error as e:
                    # 忽略UNIQUE constraint failed错误，继续执行
                    if "UNIQUE constraint failed" in str(e):
                        print(f"忽略错误: {e}")
                        continue
                    # 忽略table already exists错误，继续执行
                    if "table already exists" in str(e).lower():
                        print(f"忽略错误: {e}")
                        continue
                    # 其他错误打印并继续
                    print(f"执行语句时出错: {e}")
                    print(f"失败的语句: {statement}")
        
        # 提交事务
        conn.commit()
        print("SQL备份导入完成")
        return True
        
    except Exception as e:
        print(f"导入过程中发生错误: {e}")
        conn.rollback()
        return False
    finally:
        # 关闭连接
        conn.close()

if __name__ == "__main__":
    # 数据库路径
    db_path = "guzian_app/guzian.db"
    # SQL备份文件路径
    sql_file_path = "guzian_app/backups/guzian_backup_20251109_041948.sql"
    
    print(f"开始导入备份文件: {sql_file_path}")
    print(f"目标数据库: {db_path}")
    
    success = import_sql_backup(db_path, sql_file_path)
    
    if success:
        print("\n✅ 备份导入成功！")
    else:
        print("\n❌ 备份导入失败，请检查错误信息。")